package com.swn.common.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.management.RuntimeErrorException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

/**
 * 功能：数据库操作工具(DAO层)
 * 说明：单表增删改查功能公共方法；
 *     且可通过get方法获取spring的jdbcTemplate、namedParameterJdbcTemplate
 *     
 * @author taozhaocai
 * @Date 2016-5-6 上午11:04:30
 */

@Repository
public class DbHelper {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	/**
	 * 根据sql执行删除
	 * @param sql
	 * @return
	 */
	public int delete(String sql){
		return jdbcTemplate.update(sql);
	}
	
	/**
	 * 根据sql执行更新
	 * @param sql
	 * @return
	 */
	public int update(String sql){
		return jdbcTemplate.update(sql);
	}
	
	/**
	 * 根据具名参数和sql执行更新
	 * @param sql
	 * @param paramMap
	 * @return
	 */
	public int update(String sql,Map<String, Object> paramMap){
		return namedParameterJdbcTemplate.update(sql,paramMap);
	}
	
	/**
	 * 增加一条记录
	 * 
	 * @param paramMap
	 * @param tableName
	 * @return int
	 * 
	 * @Author taozhaocai
	 * @Date 2016-5-5 下午8:47:58
	 */
	public int saveMap(Map<String, Object> paramMap, String tableName) {
		String sql = SqlUtil.getAddNamedSql(DbTableUtil.COLCODELIST(tableName),paramMap, tableName);
		return namedParameterJdbcTemplate.update(sql, paramMap);
	}


	/**
	 * 根据主键删除一条记录
	 * 
	 * @param paramMap
	 * @param tableName
	 * @return int
	 * 
	 * @Author taozhaocai
	 * @Date 2016-5-5 下午8:48:14
	 */
	
	public int delete(Map<String, Object> paramMap, String tableName) {
		String deleteSql = DbTableUtil.PREPARED_REMOVESQL(tableName);
		return namedParameterJdbcTemplate.update(deleteSql, paramMap);
	}

	/**
	 * 更新一条记录
	 * 
	 * @param paramMap：其内必须有表主键的key,支持复合主键
	 * @param tableName
	 * @return int
	 * 
	 * @Author taozhaocai
	 * @Date 2016-5-5 下午8:48:29
	 */
	public int updateMap(Map<String, Object> paramMap, String tableName) {
		String updateSql = SqlUtil.getUpdateNamedSql(
				DbTableUtil.COLCODELIST(tableName), paramMap, tableName,
				DbTableUtil.PREPARED_PKWHERE(tableName));
		return namedParameterJdbcTemplate.update(updateSql, paramMap);
	}
	
	/**
	 * 根据具名参数更新记录
	 * 
	 * @param paramMap
	 * @param tableName
	 * @param prepared_where:更新条件,格式为具名参数，例如：“orgId=:orgId and status=:status”。
	 * @return int
	 *
	 * @Author taozhaocai
	 * @Date 2016-5-13 下午4:06:25
	 */
	public int updateMap(Map<String, Object> paramMap, String tableName,String prepared_where) {
		String updateSql = SqlUtil.getUpdateNamedSql(
				DbTableUtil.COLCODELIST(tableName), paramMap, tableName, prepared_where); 
		return namedParameterJdbcTemplate.update(updateSql, paramMap);
	}


	/**
	 * 更新或插入：有则更新，无则插入（更新时根据主键更新）
	 * 
	 * @param paramMap
	 * @param tableName
	 * @return int
	 * @Date 2016-6-12 下午5:28:10
	 */
	public int saveOrUpdate(Map<String, Object> paramMap, String tableName) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(1) FROM ").append(tableName).append(" WHERE id =:id");
		int count = namedParameterJdbcTemplate.queryForObject(sql.toString(),paramMap, Integer.class);
		if (count > 0) {
			return updateMap(paramMap, tableName);
		} else {
			return saveMap(paramMap, tableName);
		}
	}
	
	/**
	 * 更新或插入：有则更新，无则插入（注意：更新时不一定根据主键更新）
	 * 
	 * @param paramMap
	 * @param tableName
	 * @param prepared_where：更新条件,格式为具名参数，例如：“orgId=:orgId and status=:status”。
	 * @return int
	 *
	 * @Author taozhaocai
	 * @Date 2016-6-12 下午5:31:57
	 */
	public int saveOrUpdate(Map<String, Object> paramMap, String tableName,String prepared_where) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(1) FROM ").append(tableName).append(" WHERE ").append(prepared_where);
		int count = namedParameterJdbcTemplate.queryForObject(sql.toString(),paramMap, Integer.class);
		if (count > 0) {
			return updateMap(paramMap, tableName);
		} else {
			return saveMap(paramMap, tableName);
		}
	}
	
	/**
	 * 根据主键查询一条记录
	 * @param keyMap  :把主键-值放在map内进行传递
	 * @param tableName
	 * @return
	 */
	public Map<String, Object> queryMapById(Map<String, Object> keyMap,String tableName) {
		Map<String, Object> map = null;
		try {
			String querySql = DbTableUtil.PREPARED_GETSQL(tableName);
			map = namedParameterJdbcTemplate.queryForMap(querySql, keyMap);
		} catch (EmptyResultDataAccessException em) {
			return map;
		} catch (Error e) {
			throw new RuntimeErrorException(e);
		}
		return map;
	}
	
	/**
	 * 根据主键查询一条记录(操作的表只有一个主键)
	 * @param keyCode：表主键字段
	 * @param keyValue:主键值
	 * @param tableName
	 * @return
	 */
	public Map<String, Object> queryMapById(String keyCode,String keyValue,String tableName) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put(keyCode, keyValue);
		Map<String, Object> map = null;
		try {
			String querySql = DbTableUtil.PREPARED_GETSQL(tableName);
			map = namedParameterJdbcTemplate.queryForMap(querySql, paramMap);
		} catch (EmptyResultDataAccessException em) {
			return map;
		} catch (Error e) {
			throw new RuntimeErrorException(e);
		}
		return map;
	}

	/**
	 * 根据sql和分页对象获取分页列表
	 * 
	 * @param page
	 * @param sql
	 * @return
	 * @throws Exception
	 *             List<Map<String,Object>>
	 * 
	 * @Author taozhaocai
	 * @Date 2016-5-6 上午11:06:13
	 */
	/*public List<Map<String, Object>> queryPageMapList(PageListData page,String sql) throws Exception {
		String paginationSql = createPageSql(sql, page);
		return jdbcTemplate.queryForList(paginationSql);
	}*/
	
	/**
	 * 防止sql注入分页查询
	 * 
	 * @param page
	 * @param sql
	 * @param paramMap
	 * @return
	 * @throws Exception List<Map<String,Object>>
	 *
	 * @Author taozhaocai
	 * @Date 2016年6月16日 下午3:58:15
	 */
	public List<Map<String, Object>> queryPageMapList(PageListData page,String sql,Map<String, Object> paramMap) throws Exception {
		String paginationSql = createPageSql(sql, page,paramMap);
		return this.namedParameterJdbcTemplate.queryForList(paginationSql, paramMap);
	}

	/**
	 * 转化为分页sql（内包含查询总条数并给page赋值）
	 * 
	 * @param sql
	 * @param page
	 * @return
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 *             String
	 * 
	 * @Author taozhaocai
	 * @Date 2016-5-6 上午11:09:17
	 */
	private String createPageSql(String sql, PageListData page)
			throws SQLException, InstantiationException,
			IllegalAccessException, ClassNotFoundException {
		if (null == page) {
			throw new IllegalArgumentException();
		}
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("SELECT * FROM ( SELECT A.*, ROWNUM RN FROM(");
		sqlStr.append(sql)
				.append(") A WHERE ROWNUM <= ")
				.append((page.getPage() - 1) * page.getPageSize()
						+ page.getPageSize()).append(") WHERE RN >=")
				.append((page.getPage() - 1) * page.getPageSize() + 1);

		page.setCount(getRsCount(sql));
		return sqlStr.toString();
	}
	
	/**
	 * 转化为分页sql（内包含查询总条数并给page赋值）
	 * 
	 * @param sql
	 * @param page
	 * @param paramMap:具名参数map
	 * @return
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException String
	 *
	 * @Author taozhaocai
	 * @Date 2016年6月17日 下午4:57:21
	 */
	private String createPageSql(String sql, PageListData page,Map<String, Object> paramMap)
			throws SQLException, InstantiationException,
			IllegalAccessException, ClassNotFoundException {
		if (null == page) {
			throw new IllegalArgumentException();
		}
		StringBuffer sqlStr = new StringBuffer();
		/*sqlStr.append("SELECT * FROM ( SELECT A.*, ROWNUM RN FROM(");
		sqlStr.append(sql)
				.append(") A WHERE ROWNUM <= ")
				.append((page.getPage() - 1) * page.getPageSize()
						+ page.getPageSize()).append(") WHERE RN >=")
				.append((page.getPage() - 1) * page.getPageSize() + 1);*/
		sqlStr.append("select * from (").append(sql).append(") t limit ");
		sqlStr.append(page.getStart()).append(",").append(page.getLength());
		page.setCount(getRsCount(sql,paramMap));
		return sqlStr.toString();
	}

	/**
	 * 获取结果总条数
	 * 
	 * @param sql
	 * @return int
	 *
	 * @Author taozhoacai
	 * @Date 2016-5-6 上午11:12:00
	 */
	private int getRsCount(String sql) {
		int count = 0;
		StringBuffer sbr = new StringBuffer();
		sbr.append("SELECT COUNT(1) FROM (").append(sql).append(")");
		count = jdbcTemplate.queryForObject(sbr.toString(), Integer.class);
		return count;
	}
	
	/**
	 * 获取结果总条数
	 * 
	 * @param sql
	 * @param paramMap:具名参数map
	 * @return int
	 *
	 * @Author taozhaocai
	 * @Date 2016年6月17日 下午4:57:54
	 */
	private int getRsCount(String sql,Map<String, Object> paramMap) {
		int count = 0;
		StringBuffer sbr = new StringBuffer();
		sbr.append("SELECT COUNT(1) FROM (").append(sql).append(") t");
		count = this.namedParameterJdbcTemplate.queryForObject(sbr.toString(), paramMap,Integer.class);
		return count;
	}
	
	public Integer getSeqNextVal(String seqName){
		StringBuffer sql = new StringBuffer();
		sql.append("select ").append(seqName).append(".NEXTVAL FROM DUAL");
		return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
	}
	
	//------------------------------------------以下为扩展方法----------------------------------------------------------------
	/**
	 * 新增一条记录，并返回唯一自增主键值<br>
	 * 该方法适合数据库支持自增主键的数据库，如sql server
	 * 
	 * @author taozhaocai
	 * @Date 2016年3月14日  上午11:06:38
	 * @Note <这里是使用注意事项>
	 * 修改日期  修改人  修改内容
	 * <多次修改需另起一行>
	 *
	 * @param sql
	 * @return
	 *
	 * @return int
	 */
	public int addDataReturnSingleKey(final String sql){
		KeyHolder keyholder = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con)throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				return ps;
			}
		}, keyholder);
		//Map map =  keyholder.getKeys();  //可以得到联合主键的值
		//keyholder.getKeyList();//得到多条记录的主键，如添加多条记录
		return keyholder.getKey().intValue();
		
	}
	
	
	/**
	 * 新增一条记录，并返回唯一自增主键值<br>
	 * 该方法适合数据库支持自增主键的数据库，如sql server
	 * 
	 * @author taozhaocai
	 * @Date 2016年3月14日  上午11:20:56
	 * @Note <这里是使用注意事项>
	 * 修改日期  修改人  修改内容
	 * <多次修改需另起一行>
	 *
	 * @param sql :sql格式为：“insert into stu values(:name,:sex,:brith)” 冒号后的值必须与对象t的属性名称一样。
	 * 
	 * @param bean:PO对象
	 * @return
	 *
	 * @return int
	 */
	public <T> int addDataReturnSingleKey(String sql,T bean){
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		
		KeyHolder keyholder = new GeneratedKeyHolder();
		this.namedParameterJdbcTemplate.update(sql,ps,keyholder);
		//Map map =  keyholder.getKeys();  //可以得到联合主键的值
		//keyholder.getKeyList();//得到多条记录的主键，如添加多条记录
		return keyholder.getKey().intValue();
		
	}
	
	/**
	 * TODO 根据SQL delete 语句和数据的提供的删除参数来执行数据的删除
	 * 
	 * @param sql String Oracle 的 delete 语句
	 * @param paramMap Map<String, Object> delete 语句中的参数
	 * @return int 返回该执行结果影响的记录数 
	 * 
	 * @Author taozhaocai
	 * @Date 2016年3月8日 下午4:03:41 
	 * @Note 
	 * 修改日期  修改人  修改内容
	 */
	public int delete(String sql, Map<String, Object> paramMap) {
		return namedParameterJdbcTemplate.update(sql, paramMap);
	}
	
	/**
	 * 获取SqlRowSet对象（数据及数据类型都可获取）
	 * 
	 * @author taozhaocai
	 * @Date 2016年3月12日  上午11:11:28
	 * @Note <这里是使用注意事项>
	 * 修改日期  修改人  修改内容
	 * <多次修改需另起一行>
	 *
	 * @param sql              使用具名参数传递
	 * @param paramMap         参数map:map的key与具名参数一致
	 * @return
	 *
	 * @return SqlRowSet
	 */
	public  SqlRowSet queryForRowSet(String sql, Map<String, Object> paramMap){
		return this.namedParameterJdbcTemplate.queryForRowSet(sql, paramMap);
	}
	
	/**
	 * 根据SQL语句和数据的类型从数据库中查询到一条结果
	 * <p>此方法多应用于从数据检索类似于 select * from table_name where id = ? </p>
	 * 
	 * 注意：利用的是具名参数，不过不是map，而是对象，：后的字符串要和对象属性一致
	 * @param sql String SQL 查询语句
	 * @param bean Class 结果类型
	 * @return T 返回指定类型的结果
	 *
	 *调用类似：
	 *StuInfo stu = new StuInfo();
	 *stu.setId(1);
	 *StuInfo stuInfoPO = this.dbHelper.query("select id,name,age from stu_info t where t.id =:id",stu);
	 *System.out.println(stuInfoPO.getName());
	 *
	 * @Author taozhaocai
	 * @Date 2016年8月2日 下午3:13:30 
	 * @Note <这里是方法的备注>
	 * 修改日期  修改人  修改内容
	 */
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public<T> T query(String sql,T newBean){
		SqlParameterSource ps=new BeanPropertySqlParameterSource(newBean);
		return (T)namedParameterJdbcTemplate.queryForObject(sql,ps, new BeanPropertyRowMapper(newBean.getClass()));
	}
	
	
	/**
	 * 根据SQL语句和数据的类型从数据库中查询到一条结果
	 * <p>此方法多应用于从数据检索类似于 select * from table_name where id = ? </p>
	 * 
	 * 注意：利用的是具名参数，不过不是map，而是对象，：后的字符串要和对象属性一致
	 * @param sql String SQL 查询语句
	 * @param bean Class 结果类型
	 * @return List<T> 返回指定类型的结果
	 *
	 *调用类似：
	 *StuInfo stu = new StuInfo();
	 *stu.setClassId(1);
	 *List<StuInfo> list = this.dbHelper.queryList("select id,name,age from stu_info where class_id=:classId",stu);
	 *
	 * @Author taozhaocai
	 * @Date 2016年8月2日 下午3:27:33 
	 * @Note <这里是方法的备注>
	 * 修改日期  修改人  修改内容
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T> List<T>  queryList(String sql,T newBean){
	       SqlParameterSource ps=new BeanPropertySqlParameterSource(newBean);
	       return namedParameterJdbcTemplate.query(sql, ps, new BeanPropertyRowMapper(newBean.getClass()));
	}
	
	
	
	/**
	 * TODO 根据 SQL 语句从数据库中检索出一条记录
	 * 
	 * @param sql String SQL 查询语句
	 * @return Map<String,Object> 返回检索的结果
	 *
	 * @Author taozhaocai
	 * @Date 2016年3月8日 下午4:33:06 
	 * @Note <这里是方法的备注>
	 * 修改日期  修改人  修改内容
	 */
	public Map<String, Object> queryForMap(String sql)throws Exception{
		try{
			return this.jdbcTemplate.queryForMap(sql);
		}catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * TODO 根据 SQL 和提供的参数语句从数据库中检索出一条记录
	 * 
	 * @param sql String SQL 查询语句
	 * @param paramMap Map<String,Object>  语句中的参数
	 * @return Map<String,Object> 返回检索的结果
	 *
	 * @Author taozhaocai
	 * @Date 2016年3月8日 下午4:35:35 
	 * @Note <这里是方法的备注>
	 * 修改日期  修改人  修改内容
	 */
	public Map<String, Object> queryForMap(String sql,Map<String, Object> paramMap) throws Exception{
		try{
			return this.namedParameterJdbcTemplate.queryForMap(sql,paramMap);
		}catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * TODO 根据 SQL 和提供的参数语句从数据库中检索出数据表中的记录集合
	 * 
	 * @param sql String SQL 查询语句
	 * @param paramMap Map<String,Object>  语句中的参数
	 * @return List<Map<String,Object>> 检索出数据表中的记录集合
	 *
	 * @Author taozhaocai
	 * @Date 2016年3月8日 下午4:45:45 
	 * @Note <这里是方法的备注>
	 * 修改日期  修改人  修改内容
	 */
	public List<Map<String, Object>> queryForList(String sql,Map<String, Object> paramMap){
		return namedParameterJdbcTemplate.queryForList(sql, paramMap);
	}	

	/**
	 * 根据 SQL 和提供的参数语句往数据库中执行一条语句
	 * 
	 * @param sql String SQL 执行语句
	 *
	 * @Author taozhaocai
	 * @Date 2016年3月8日 下午4:46:11 
	 * @Note <这里是方法的备注>
	 * 修改日期  修改人  修改内容
	 */
	public void execute(String sql) {
		this.jdbcTemplate.execute(sql);
	}
	
	/**
	 * 批量执行sql
	 * 
	 * 
	 * @author taozhaocai
	 * @Date 2016年3月12日  上午11:23:28
	 * @Note <这里是使用注意事项>
	 * 修改日期  修改人  修改内容
	 * <多次修改需另起一行>
	 *
	 * @param sql
	 * @return
	 *
	 * @return int[]
	 */
	public int[] batchUpdate(String...sql){
		return this.jdbcTemplate.batchUpdate(sql);
	}

	/**
	 * TODO 根据 存储过程名称和参数调用存储过程返回需要的结果 （记录或游标）<br>
	 * 
	 * <p>在确定记录为单条记录返回类型为 Map<String, Object> 进行取值; 
	 * 不确定的情况下结果类型为 Map<String, List<Map<String, Object>>> 返回将结果类型进行强转型为 List<Map<String, Object>></p>
	 * 
	 * <pre>
	 *     List<Map<String, Object>> mapList == (List<Map<String, Object>>)map.get("key");
	 * </pre>
	 * 
	 * @param procName String 存储过程名称
	 * @param mapParams Map<String, Object> 输入参数
	 * @return Map<String,Object> 返回存储过程执行的结果
	 *
	 * @Author taozhaocai
	 * @Date 2016年3月8日 下午4:46:06 
	 * @Note <这里是方法的备注>
	 * 修改日期  修改人  修改内容
	 */
	public Map<String, Object> queryProcForMap(String procName,Map<String, Object> paramMap) {
		 SimpleJdbcCall call = new SimpleJdbcCall(this.jdbcTemplate.getDataSource()).withProcedureName(procName);
		 SqlParameterSource in = new MapSqlParameterSource(paramMap);
		 return call.execute(in);
	}
	
	
	/**
	 * 检索下一个序列
	 * @param seqName
	 * @return
	 */
	public int querySeqNextVal(String seqName){
		String sql = "select "+seqName +"_seq.nextval from dual";
		Integer seqValue = this.getJdbcTemplate().queryForObject(sql, Integer.class);
		return seqValue.intValue();
	}
	/**
	 * 检索现在序列
	 * @param seqName
	 * @return
	 */
	public int querySeqCurrVal(String seqName){
		String sql = "select "+seqName +"_seq.currval from dual";
		Integer seqValue = this.getJdbcTemplate().queryForObject(sql, Integer.class);
		return seqValue.intValue();
	}
	
	//----------------------------------------------------------------------------------------------------------
	

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
		return namedParameterJdbcTemplate;
	}

	public void setNamedParameterJdbcTemplate(
			NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
		this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
	}

}
